#include "config.h"

#ifdef ENABLE_MYSQL

#include <boost/lexical_cast.hpp>

#ifdef WINDOWS
#include <winsock2.h>
#include <windows.h>
#endif

#include <mysql.h>

#include "MySQLDatabase.h"
#include "MySQLQuery.h"

using boost::lexical_cast;

/**
 * http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html
 */
MySQLDatabase::MySQLDatabase (PString host, PString port, PString database, PString username, PString password) : AbstractDatabase(host, port, database,username, password)
{
    //this->supportGeoDataTypes = "'point','linestring','polygon','multipoint','multilinestring', 'multipolygon', 'geometrycollection','geometry'";
    this->supportGeoDataTypes = "'geometry'";
    this->dbDriverName = new String("MYSQL");
	this->conn = mysql_init(NULL);
	this->connected = true;

	//auto reconnecting
    my_bool reconnect = 1;
    mysql_options(this->conn, MYSQL_OPT_RECONNECT, &reconnect);
    if (!mysql_real_connect(this->conn, this->host->c_str(), this->username->c_str(), this->password->c_str(), this->database->c_str(), atoi(this->port->c_str()), NULL, CLIENT_MULTI_STATEMENTS)) {
        std::cout <<  mysql_error(this->conn) << std::endl;
		this->connected = false;
        //this->error("%s %s", "test1","test2");
	}
	/*
      CR_COMMANDS_OUT_OF_SYNC
      Commands were executed in an improper order.
    *
      CR_SERVER_GONE_ERROR
      The MySQL server has gone away.
    *
      CR_SERVER_LOST
      The connection to the server was lost during the query.
    *
      CR_UNKNOWN_ERROR
      An unknown error occurred.
    *
      ER_UNKNOWN_COM_ERROR
      The MySQL server doesn't implement this command (probably an old server).
    *
      ER_ACCESS_DENIED_ERROR
      The user or password was wrong.
    *
      ER_BAD_DB_ERROR
      The database didn't exist.
    *
      ER_DBACCESS_DENIED_ERROR
      The user did not have access rights to the database.
    *
      ER_WRONG_DB_NAME
      The database name was too long.*/
}

/**
 *
 **/
MySQLDatabase::~MySQLDatabase() {
	mysql_close(this->conn);
}

/**
 *
 **/
bool MySQLDatabase::isConnected(String database) {
	return this->connected;
}

/**
 *
 **/
PQuery MySQLDatabase::query(String sqlQuery)
{
	return new MySQLQuery(this, this->database, &sqlQuery);
}

/**
 *
 **/
PQuery MySQLDatabase::select(PString database, PString schema, PString table, int option)
{
    String query  = "SELECT ";
    TableInfo info = this->getTableInfoOf(database, schema, table);
    if(option & OPTION_ONLY_ATTR || option & OPTION_ALL)
    {
        query.append(info.fields.at(0).name);
        for(unsigned int i = 1; i < info.fields.size(); i++)
        {
            query.append(","+info.fields.at(i).name);
        }
    }
    else if(option & OPTION_ONLY_GEOM || option & OPTION_ALL)
    {
        query.append("AsText("+info.geometry.name+")");
    }

    query.append(" FROM `"+*database+"`.`"+*table+"`");
    return this->query(query);
}

/**
 *
 **/
PQuery MySQLDatabase::getTableInfoQuery(PString database, PString schema, PString table)
{
    String column_name = "";
    String columnQuery = "SELECT COLUMN_NAME FROM information_schema.`COLUMNS` as col WHERE  col.`TABLE_SCHEMA` =  '"+*schema+"' AND col.`TABLE_NAME` = '"+*table+"' AND lower(col.`DATA_TYPE`) IN("+this->supportGeoDataTypes+")";
    MySQLQuery result(this, database, &columnQuery);

    while(result.hasMore())
    {
        PDataRow row = result.fetch();
        column_name = row->getFieldValue(0);
    }

    String sqlQuery = "SELECT ";
    if(column_name.compare("") == 0)
    {
        sqlQuery.append("'NO' as has_geometry, 'NO' as has_srid,");
    }
    else
    {
        sqlQuery.append("'YES' as has_geometry, CASE WHEN (SELECT SRID(sr."+column_name+") FROM `"+*schema+"`.`"+*table+"` as sr LIMIT 1) > 0 AND (SELECT SRID(sr."+column_name+") FROM `"+*schema+"`.`"+*table+"` as sr LIMIT 1) < 30000 THEN 'YES' ELSE 'NO' END as has_srid,");
    }
    sqlQuery.append("(SELECT (ta.`DATA_LENGTH` + ta.`INDEX_LENGTH`) AS has FROM information_schema.`TABLES` AS ta WHERE ta.`TABLE_SCHEMA` =  '"+*schema+"' AND ta.`TABLE_NAME` =  '"+*table+"') as table_size");
    return this->query(sqlQuery);
}

/**
 *
 **/
PQuery MySQLDatabase::getFieldListQuery(PString database, PString schema, PString table)
{
    return this->query("SELECT `COLUMN_NAME`, CASE WHEN lower(`DATA_TYPE`) IN("+this->supportGeoDataTypes+") THEN 'geometry' ELSE `DATA_TYPE` END as DATA_TYPE FROM  information_schema.`COLUMNS`  WHERE  `TABLE_SCHEMA` =  '"+*schema+"' AND  `TABLE_NAME` =  '"+*table+"'");
}

/**
 *
 **/
PQuery MySQLDatabase::listDatabases(){
    return this->query("SELECT SCHEMA_NAME FROM information_schema.schemata WHERE SCHEMA_NAME <> \"information_schema\"");
}

/**
 *
 **/
PQuery MySQLDatabase::listSchemata(PString database) {
    return this->query("SELECT SCHEMA_NAME FROM information_schema.schemata WHERE SCHEMA_NAME = '"+*database+"'");
}

/**
 *
 **/
PQuery MySQLDatabase::listTables(PString database, PString schema) {
    return this->query("SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='"+*database+"'");
}

/**
 *
 **/
bool MySQLDatabase::exists(PString database, PString schema, PString table)
{
    if(database->compare("") == 0) return true; // ROOT
    if(database->size() != 0 && schema->size() != 0 && database->compare(*schema) != 0) return false;
    String sql = "SELECT SCHEMA_NAME FROM information_schema.schemata WHERE SCHEMA_NAME = \""+*database+"\"";
    if(schema->compare("") != 0 && table->compare("") == 0)
    {
        sql = "SELECT SCHEMA_NAME FROM information_schema.schemata WHERE SCHEMA_NAME = \""+*schema+"\"";
    }
    if(schema->compare("") != 0 && table->compare("") != 0)
    {
        sql = "SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA=\""+*database+"\" AND TABLE_NAME = \""+*table+"\"";
    }
    MySQLQuery query(this, this->database, &sql);
    return (query.getNumRows() != 0); // 0 = not exists, 1 = exists
}

void MySQLDatabase::dropTable(PString database, PString schema, PString table)
{
    PQuery query = this->query("DROP TABLE IF EXISTS `"+*schema+"`.`"+*table+"`");
    delete query;
}


#endif
